package worldcuptour.database;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DbHelper extends SQLiteOpenHelper{

	private static final int DATABASE_VERSION = 1;
	private static final String DATABASE_NAME = "WorldCupTour";
	
	//TABLES
	public static final String DATABASE_TABLE_CITY = "city";  
	public static final String DATABASE_TABLE_ATRACTION = "atraction";  
	public static final String DATABASE_TABLE_TYPE_ATRACTION = "type_atraction"; 
	
	//Campos da table city
	public static final String ID_CITY = "id";
	public static final String NOME_CITY = "nome";
	
	//campos da table atraction
	public static final String ID_ATRACTION = "id";
	public static final String DESCRIPTION_ATRACTION = "description";
	public static final String CITY = "id_city";
	public static final String IMG = "img";
	public static final String TYPE_ATRACTION = "id_type_atraction";
	
	//campos da table type_atraction
	public static final String ID_TYPE_ATRACTION = "id";
	public static final String DESCRIPTION = "description";
	
	//script to create tables
	private static final String DATABASE_CREATE = "create table" + DATABASE_TABLE_CITY + "("
			+ ID_CITY + "integer primary key autoincrement,"
			+ NOME_CITY + "text not null);"
			
			+ "create table" + DATABASE_TABLE_TYPE_ATRACTION + "(" 
			+ ID_TYPE_ATRACTION + "integer primary key autoincrement,"
			+ DESCRIPTION + "text not null);"
			
			+ "create table" + DATABASE_TABLE_ATRACTION + "("
			+ ID_ATRACTION + "integer primary key autoincrement,"
			+ DESCRIPTION_ATRACTION + "text not null,"
			+ CITY + "integer not null,"
			+ IMG + "BLOB,"
			+ TYPE_ATRACTION + "integer,"
			+"foreign key(" + TYPE_ATRACTION + ") references " + DATABASE_TABLE_TYPE_ATRACTION + "(" + ID_TYPE_ATRACTION + ")";
			
	
	 public DbHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(DATABASE_CREATE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		  Log.w(DbHelper.class.getName(),
	                 "Upgrading database from version " + oldVersion + " to "
	                 + newVersion + ", which will destroy all old data");
	                db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_CITY);
	                db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_TYPE_ATRACTION);
	                db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_ATRACTION);
	                onCreate(db);
	}

}
